const { pool } = require('../config/db')
const Router = require('koa-router')
const router = new Router({ prefix: '/blog' })

// 中间件：验证用户是否登录
const authMiddleware = require('../middleware/auth')

// 获取单篇文章
const getArticleById = async (ctx) => {
  try {
    const { id } = ctx.params;
    const articleId = parseInt(id);

    // 参数验证
    if (isNaN(articleId) || articleId <= 0) {
      ctx.status = 400;
      return ctx.body = {
        success: false,
        message: '无效的文章ID',
        code: 400
      };
    }

    // 查询文章基本信息和标签信息
    const [articleRows] = await pool.query(`
      SELECT 
        a.id,
        a.title,
        a.content,
        c.name as category,
        a.category_id,
        a.create_time,
        a.author_id,
        a.update_time,
        a.status,
        u.username as author_name
      FROM articles a 
      LEFT JOIN categories c ON a.category_id = c.id
      LEFT JOIN users u ON a.author_id = u.id
      WHERE a.id = ?
    `, [articleId]);

    if (articleRows.length === 0) {
      ctx.status = 404;
      return ctx.body = {
        success: false,
        message: '文章不存在',
        code: 404
      };
    }

    // 查询文章的标签列表
    const [tagRows] = await pool.query(`
      SELECT t.id, t.name 
      FROM tags t 
      JOIN article_tags at ON t.id = at.tag_id 
      WHERE at.article_id = ? 
      ORDER BY t.name
    `, [articleId]);

    // 组合返回数据
    const article = {
      ...articleRows[0],
      tags: tagRows.map(tag => tag.name), // 返回标签名称数组
      tagIds: tagRows.map(tag => tag.id) // 同时返回标签ID数组
    };

    ctx.body = {
      success: true,
      data: article,
      message: '文章获取成功',
      code: 200
    };

  } catch (error) {
    console.error('获取文章失败:', error);
    ctx.status = 500;
    ctx.body = {
      success: false,
      message: '服务器内部错误',
      code: 500
    };
  }
};

// 初始化缓存对象
const articleListCache = {
  cacheTime: 5 * 60 * 1000 // 5分钟缓存
};

// 前台文章列表 - 修复分类筛选
const getArticleList = async (ctx) => {
  try {
    // 获取查询参数（添加tags参数）
    const {
      page = 1,
      pageSize = 10,
      keyword = '',
      category_id = '',
      tags = '', // 新增标签参数
      sortBy = 'create_time',
      sortOrder = 'DESC'
    } = ctx.query;

    // 参数验证
    const pageNum = parseInt(page);
    const limitNum = parseInt(pageSize);

    if (isNaN(pageNum) || pageNum < 1) {
      ctx.status = 400;
      return ctx.body = {
        success: false,
        message: '参数错误：页码必须是正整数',
        code: 400
      };
    }

    if (isNaN(limitNum) || limitNum < 1 || limitNum > 100) {
      ctx.status = 400;
      return ctx.body = {
        success: false,
        message: '参数错误：每页条数必须在1-100之间',
        code: 400
      };
    }

    // 构建缓存key（基于查询参数，添加tags）
    const cacheKey = `article_list_${pageNum}_${limitNum}_${keyword}_${category_id}_${tags}_${sortBy}_${sortOrder}`;
    const now = Date.now();

    // 检查缓存是否有效
    if (articleListCache[cacheKey] &&
      articleListCache[cacheKey].lastUpdate &&
      (now - articleListCache[cacheKey].lastUpdate) < articleListCache.cacheTime) {
      console.log('使用缓存的文章列表');
      ctx.body = articleListCache[cacheKey].data;
      return;
    }

    console.log('查询数据库获取文章列表');
    const offset = (pageNum - 1) * limitNum;

    // 构建查询条件
    let whereConditions = [];
    let queryParams = [];

    // 关键词搜索（标题和内容）
    if (keyword && keyword.trim()) {
      whereConditions.push('(a.title LIKE ? OR a.content LIKE ?)');
      const searchKeyword = `%${keyword.trim()}%`;
      queryParams.push(searchKeyword, searchKeyword);
    }

    // 分类筛选（使用category_id）
    if (category_id && !isNaN(parseInt(category_id))) {
      whereConditions.push('a.category_id = ?');
      queryParams.push(parseInt(category_id));
    }

    // 标签筛选 - 新增功能
    if (tags && tags.trim()) {
      const tagList = tags.split(',').map(tag => tag.trim()).filter(tag => tag);
      if (tagList.length > 0) {
        whereConditions.push(`a.id IN (
          SELECT DISTINCT at.article_id 
          FROM article_tags at 
          JOIN tags t ON at.tag_id = t.id 
          WHERE t.name IN (${tagList.map(() => '?').join(',')})
        )`);
        queryParams.push(...tagList);
      }
    }

    // 构建WHERE子句
    const whereClause = whereConditions.length > 0 ? `WHERE ${whereConditions.join(' AND ')}` : '';

    // 验证排序字段（只允许特定字段）
    const allowedSortFields = ['create_time', 'title', 'id'];
    const sortField = allowedSortFields.includes(sortBy) ? sortBy : 'create_time';
    const sortDirection = sortOrder.toUpperCase() === 'ASC' ? 'ASC' : 'DESC';

    // 查询总数
    const countQuery = `SELECT COUNT(*) as total FROM articles a ${whereClause}`;
    const [countResult] = await pool.query(countQuery, queryParams);
    const total = countResult[0].total;

    // 查询分页数据
    let rows = [];
    if (total > 0) {
      const dataQuery = `
        SELECT a.id, a.title, a.create_time, c.name as category
        FROM articles a
        LEFT JOIN categories c ON a.category_id = c.id
        ${whereClause}
        ORDER BY a.${sortField} ${sortDirection}
        LIMIT ? OFFSET ?
      `;
      const [dataRows] = await pool.query(dataQuery, [...queryParams, limitNum, offset]);
      rows = dataRows;
    }

    // 格式化数据
    const formattedArticles = rows.map(article => ({
      id: article.id,
      title: article.title,
      createTime: article.create_time,
      category: article.category || '未分类'
    }));

    // 计算总页数
    const totalPages = Math.ceil(total / limitNum);

    // 构建响应数据
    const responseData = {
      success: true,
      data: {
        list: formattedArticles,
        total: total,
        page: pageNum,
        pageSize: limitNum,
        totalPages: totalPages
      },
      cached: false,
      code: 200
    };

    // 更新缓存
    articleListCache[cacheKey] = {
      data: responseData,
      lastUpdate: now
    };

    ctx.body = responseData;

  } catch (error) {
    console.error('获取文章列表失败:', error);
    ctx.status = 500;
    ctx.body = {
      success: false,
      message: '获取文章列表失败',
      code: 500
    };
  }
};

// 修改缓存清理函数，支持新的缓存策略
const clearArticleListCache = () => {
  // 清空所有文章列表相关的缓存
  Object.keys(articleListCache).forEach(key => {
    if (key !== 'cacheTime') {
      delete articleListCache[key];
    }
  });
  console.log('文章列表缓存已清理');
};

// 创建文章（需要登录）- 适配新数据库结构
const createArticle = async (ctx) => {
  const { title, content, category_id, tags } = ctx.request.body

  // 参数验证
  if (!title || !content || !category_id) {
    ctx.status = 400
    return ctx.body = {
      success: false,
      message: '标题、内容、分类ID不能为空',
      code: 400
    }
  }

  // 验证分类ID是否为数字
  const categoryId = parseInt(category_id);
  if (isNaN(categoryId) || categoryId <= 0) {
    ctx.status = 400
    return ctx.body = {
      success: false,
      message: '分类ID必须是有效的数字',
      code: 400
    }
  }

  const tagsArray = [];
  if (typeof tags === 'string') {
    // 如果是字符串
    tagsArray.push(tags);
  } else if (Array.isArray(tags)) {
    tagsArray = tags;
  } else {
    return ctx.body = {
      success: false,
      message: '标签传入格式错误',
      code: 400
    }
  }

  const authorId = ctx.state.user?.userId
  if (!authorId) {
    ctx.status = 400
    return ctx.body = {
      success: false,
      message: '用户ID缺失，无法创建文章',
      code: 400
    }
  }

  const connection = await pool.getConnection();

  try {
    // 开始事务
    await connection.beginTransaction();

    // 插入文章
    const [result] = await connection.query(
      'INSERT INTO articles (title, content, category_id, author_id) VALUES (?, ?, ?, ?)',
      [title, content, categoryId, authorId]
    );

    const articleId = result.insertId;

    // 处理标签关联
    if (tagsArray.length > 0) {
      // 获取或创建标签
      const tagIds = [];
      for (const tagName of tagsArray) {
        if (!tagName.trim()) continue;

        // 检查标签是否存在
        const [existingTags] = await connection.query(
          'SELECT id FROM tags WHERE name = ?',
          [tagName.trim()]
        );

        let tagId;
        if (existingTags.length > 0) {
          tagId = existingTags[0].id;
        } else {
          // 创建新标签
          const [newTag] = await connection.query(
            'INSERT INTO tags (name) VALUES (?)',
            [tagName.trim()]
          );
          tagId = newTag.insertId;
        }
        tagIds.push(tagId);
      }

      // 插入文章-标签关联
      if (tagIds.length > 0) {
        const tagRelations = tagIds.map(tagId => [articleId, tagId]);
        await connection.query(
          'INSERT INTO article_tags (article_id, tag_id) VALUES ?',
          [tagRelations]
        );

        // 更新标签使用计数
        await connection.query(
          'UPDATE tags SET count = count + 1 WHERE id IN (?)',
          [tagIds]
        );
      }
    }

    await connection.commit();

    // 清理缓存
    clearArticleListCache();

    ctx.status = 201
    ctx.body = {
      success: true,
      id: articleId,
      message: '文章创建成功',
      code: 200
    };

  } catch (error) {
    await connection.rollback();
    console.error('创建文章失败:', error);
    ctx.status = 500
    ctx.body = {
      success: false,
      message: '创建文章失败',
      code: 500
    };
  } finally {
    connection.release();
  }
}

// 更新文章（需要登录）- 适配新数据库结构
const updateArticle = async (ctx) => {
  const connection = await pool.getConnection();

  try {
    const { id } = ctx.params;
    const { title, content, category_id, tags } = ctx.request.body;

    // 参数验证 - 文章ID
    const articleId = parseInt(id);
    if (isNaN(articleId) || articleId <= 0) {
      ctx.status = 400;
      return ctx.body = {
        success: false,
        message: '无效的文章ID',
        code: 400
      };
    }

    // 参数验证 - 必填字段
    if (!title || !content || !category_id) {
      ctx.status = 400;
      return ctx.body = {
        success: false,
        message: '标题、内容、分类ID不能为空',
        code: 400
      };
    }

    // 验证分类ID
    const categoryId = parseInt(category_id);
    if (isNaN(categoryId) || categoryId <= 0) {
      ctx.status = 400;
      return ctx.body = {
        success: false,
        message: '分类ID必须是有效的数字',
        code: 400
      };
    }

    const tagsArray = [];
    if (typeof tags === 'string') {
      // 如果是字符串
      tagsArray.push(tags);
    } else if (Array.isArray(tags)) {
      tagsArray = tags;
    } else {
      return ctx.body = {
        success: false,
        message: '标签传入格式错误',
        code: 400
      }
    }

    // 开始事务
    await connection.beginTransaction();

    // 检查文章是否存在
    const [check] = await connection.query('SELECT id FROM articles WHERE id = ?', [articleId]);
    if (check.length === 0) {
      ctx.status = 404;
      return ctx.body = {
        success: false,
        message: '文章不存在',
        code: 404
      };
    }

    // 更新文章基本信息
    await connection.query(
      'UPDATE articles SET title=?, content=?, category_id=? WHERE id=?',
      [title, content, categoryId, articleId]
    );

    // 处理标签更新
    // 1. 获取当前文章的标签关联
    const [currentTags] = await connection.query(
      'SELECT tag_id FROM article_tags WHERE article_id = ?',
      [articleId]
    );
    const currentTagIds = currentTags.map(row => row.tag_id);

    // 2. 处理新标签
    const newTagIds = [];
    for (const tagName of tagsArray) {
      if (!tagName.trim()) continue;

      const [existingTags] = await connection.query(
        'SELECT id FROM tags WHERE name = ?',
        [tagName.trim()]
      );

      let tagId;
      if (existingTags.length > 0) {
        tagId = existingTags[0].id;
      } else {
        const [newTag] = await connection.query(
          'INSERT INTO tags (name) VALUES (?)',
          [tagName.trim()]
        );
        tagId = newTag.insertId;
      }
      newTagIds.push(tagId);
    }

    // 3. 找出需要删除和添加的标签
    const tagsToDelete = currentTagIds.filter(id => !newTagIds.includes(id));
    const tagsToAdd = newTagIds.filter(id => !currentTagIds.includes(id));

    // 4. 删除旧的标签关联
    if (tagsToDelete.length > 0) {
      await connection.query(
        'DELETE FROM article_tags WHERE article_id = ? AND tag_id IN (?)',
        [articleId, tagsToDelete]
      );

      // 减少标签使用计数
      await connection.query(
        'UPDATE tags SET count = GREATEST(count - 1, 0) WHERE id IN (?)',
        [tagsToDelete]
      );
    }

    // 5. 添加新的标签关联
    if (tagsToAdd.length > 0) {
      const tagRelations = tagsToAdd.map(tagId => [articleId, tagId]);
      await connection.query(
        'INSERT INTO article_tags (article_id, tag_id) VALUES ?',
        [tagRelations]
      );

      // 增加标签使用计数
      await connection.query(
        'UPDATE tags SET count = count + 1 WHERE id IN (?)',
        [tagsToAdd]
      );
    }

    await connection.commit();

    // 清理缓存
    clearArticleListCache();

    ctx.body = {
      success: true,
      message: '文章更新成功',
      code: 200
    };

  } catch (error) {
    await connection.rollback();
    console.error('更新文章失败:', error);
    ctx.status = 500;
    ctx.body = {
      success: false,
      message: '服务器内部错误',
      code: 500
    };
  } finally {
    connection.release();
  }
}

// 删除文章（需要登录）
const deleteArticle = async (ctx) => {
  const { id } = ctx.params
  const [check] = await pool.query('SELECT * FROM articles WHERE id = ?', [id])
  if (check.length === 0) {
    ctx.status = 404
    return ctx.body = { message: '文章不存在', code: 404 }
  }
  await pool.query('DELETE FROM articles WHERE id = ?', [id])
  // 清理缓存
  clearArticleListCache();
  ctx.body = { message: '文章删除成功', code: 200 }
}

// 分页+查询文章 - 修复分类搜索
const searchArticles = async (ctx) => {
  console.log('=== 搜索调试信息 ===');
  console.log('原始查询参数:', ctx.query);
  console.log('请求URL:', ctx.url);
  console.log('请求方法:', ctx.method);
  const {
    page = 1,
    pageSize = 10,
    keyword,
    category_id,      // 改为分类ID搜索
    tags,          // 按标签搜索
    startDate,     // 开始日期
    endDate,       // 结束日期
    sortBy = 'create_time',  // 排序字段
    sortOrder = 'DESC'       // 排序方式
  } = ctx.query;

  let sql = `
    SELECT a.*, c.name as category_name, u.username as author_name 
    FROM articles a 
    LEFT JOIN categories c ON a.category_id = c.id
    LEFT JOIN users u ON a.author_id = u.id 
    WHERE 1=1
  `;

  let countSql = `
    SELECT COUNT(*) AS total 
    FROM articles a 
    WHERE 1=1
  `;

  const params = [];
  const countParams = [];

  // 关键词搜索（标题和内容）
  if (keyword?.trim()) {
    sql += ' AND (a.title LIKE ? OR a.content LIKE ?)';
    countSql += ' AND (a.title LIKE ? OR a.content LIKE ?)';
    params.push(`%${keyword.trim()}%`, `%${keyword.trim()}%`);
    countParams.push(`%${keyword.trim()}%`, `%${keyword.trim()}%`);
  }

  // 分类搜索（使用category_id）
  if (category_id) {
    const categoryId = parseInt(category_id);
    if (!isNaN(categoryId) && categoryId > 0) {
      sql += ' AND a.category_id = ?';
      countSql += ' AND a.category_id = ?';
      params.push(categoryId);
      countParams.push(categoryId);
    }
  }

  // 标签搜索（保持关联表查询）
  if (tags) {
    const tagList = tags.split(',').map(tag => tag.trim()).filter(tag => tag);
    if (tagList.length > 0) {
      sql += ` AND a.id IN (
        SELECT DISTINCT at.article_id 
        FROM article_tags at 
        JOIN tags t ON at.tag_id = t.id 
        WHERE t.name IN (${tagList.map(() => '?').join(',')})
      )`;
      countSql += ` AND a.id IN (
        SELECT DISTINCT at.article_id 
        FROM article_tags at 
        JOIN tags t ON at.tag_id = t.id 
        WHERE t.name IN (${tagList.map(() => '?').join(',')})
      )`;
      params.push(...tagList);
      countParams.push(...tagList);
    }
  }

  // 日期范围搜索
  if (startDate) {
    sql += ' AND a.create_time >= ?';
    countSql += ' AND a.create_time >= ?';
    params.push(startDate);
    countParams.push(startDate);
  }

  if (endDate) {
    sql += ' AND a.create_time <= ?';
    countSql += ' AND a.create_time <= ?';
    params.push(endDate);
    countParams.push(endDate);
  }

  // 排序
  const validSortFields = ['create_time', 'title', 'category_id'];
  const validSortOrder = ['ASC', 'DESC'];
  const orderBy = validSortFields.includes(sortBy) ? sortBy : 'create_time';
  const order = validSortOrder.includes(sortOrder.toUpperCase()) ? sortOrder.toUpperCase() : 'DESC';

  sql += ` ORDER BY a.${orderBy} ${order}`;

  // 分页
  const offset = (page - 1) * pageSize;
  sql += ' LIMIT ? OFFSET ?';
  params.push(Number(pageSize), Number(offset));

  const [rows] = await pool.query(sql, params);
  const [countResult] = await pool.query(countSql, countParams);

  // 为每篇文章查询标签信息
  const articlesWithTags = await Promise.all(rows.map(async (article) => {
    const [tagRows] = await pool.query(`
      SELECT t.id, t.name 
      FROM tags t 
      JOIN article_tags at ON t.id = at.tag_id 
      WHERE at.article_id = ? 
      ORDER BY t.name
    `, [article.id]);

    return {
      ...article,
      tags: tagRows.map(tag => tag.name), // 标签名称数组
      tagIds: tagRows.map(tag => tag.id)  // 标签ID数组
    };
  }));

  ctx.body = {
    success: true,
    data: {
      list: articlesWithTags,
      total: countResult[0].total,
      page: Number(page),
      pageSize: Number(pageSize)
    },
    message: '文章搜索成功',
    code: 200
  };
}

// 前台公开接口（无权限）
router.get('/articles/simple', getArticleList)
router.get('/articles/:id', getArticleById)
router.get('/articles', searchArticles)

// 后台管理接口（需要权限）
router.post('/articles', authMiddleware, createArticle)
router.put('/articles/:id', authMiddleware, updateArticle)
router.delete('/articles/:id', authMiddleware, deleteArticle)

// 导出路由实例
module.exports = router